<?php
/**
 * Created by PhpStorm.
 * User: 
 * Date: 
 * Time: 
 */

namespace app\services\model;

use think\Config;
use think\Model;
use think\Db;

class CaseList extends Model
{
    // 设置当前模型对应的完整数据表名称
    protected $table = 'case_list';
    /**
     * [caseList description]
     * @param  [type] $userId  [description]
     * @param  [type] $reqData [description]
     * @return [type]          [description]
     */
    public function caseList($userId,$reqData){
        //1查询用户角色
        $roleOb = new \app\services\model\AdminRoles();
        $role = $roleOb->roleByRoleid($reqData['roleId']);
        if(!$role){
            $ret['status'] = 2001;
            return $ret;
        }
        $roleName = $role['role'];
        // $config = Config::get('api')['roleCase'];
        // var_dump($config);exit;
        // $info = $config[$roleName];
        // if(!$info){
        //     $ret['status'] = 3001;
        //     return $ret;
        // }
        // echo $info;exit;
        $object = Db::table("case_list list")
            ->join("admin_users base1",'list.import_user_id = base1.id','left')
            ->join("admin_users base2",'list.examine_user_id = base2.id','left')
            ->join("admin_users base3",'list.final_user_id = base3.id','left'); 
        if(isset($reqData['type'])&& $reqData['type']!=''){
            if($reqData['type']==1){
                //未完成
                $object = $object->where("
                    (list.import_user_id=$userId and list.status >=0 and list.status<=3) or 
                    ((list.examine_user_id = $userId and list.status >=1 and list.status<=3) or(list.examine_user_id=0 and list.status=2)) or
                    ((list.final_user_id =$userId and list.status>=1 and list.status<=3 ) or (list.final_user_id=0 and list.status=3))");
                
            }
            else{
                //已完成
                $object = $object->where("
                    (list.import_user_id=$userId  or
                    list.examine_user_id=$userId   or 
                    list.final_user_id=$userId)
                    and list.status=4");
            }
        }

        // if($info=="import"){
        //     //录入员
        //     $object = $object->where("list.import_user_id",$userId);
        //     if($reqData['type']!=''){
        //         if($reqData['type']==1){
        //             // 未完成
        //             $object = $object->where("list.status",">=",0);
        //             $object = $object->where("list.status","<=",3);                    
        //         }
        //         else{
        //             //已完成
        //             $object = $object->where("list.status",4);
        //         }
        //     }
        // }
        // else if($info=='examine'){
        //     //初审员
        //     if(!empty($reqData['type']) &&$reqData['type'] ==1){
        //         // 未完成
        //         $object = $object->where("(list.examine_user_id = $userId and list.status >=1 and list.status<=3) or(list.examine_user_id=0 and list.status=2)");
                    
        //     }
        //     else{
        //         //已完成
        //         $object = $object->where("list.examine_user_id",$userId);
        //         $object = $object->where("list.status",4);
        //     }

        // }
        // else if($info=='final'){
        //     // 终审
        //     if(!empty($reqData['type']) &&$reqData['type'] ==1){
        //         // 未完成
        //         $object = $object->where("(list.final_user_id =$userId and list.status>=1 and list.status<=3 ) or (list.final_user_id=0 and list.status=3)"); 
        //     }
        //     else{
        //         //已完成
        //         $object = $object->where("list.final_user_id",$userId); 
        //         $object = $object->where("list.status",4);
        //     }
        // }
        // else{
        //     //所有
        //     if($reqData['type']==1){
        //         // 未完成
        //         $object = $object->where("list.status",">=",0);                
        //         $object = $object->where("list.status","<=",3);                
        //     }
        //     else{
        //         //已完成
        //         $object = $object->where("list.status",4);
        //     }
        // }

       
        if(isset($reqData['title']) &&$reqData['title']!=''){
            $object = $object->whereLike("list.title","%".$reqData['title']."%");
        } 

        if($reqData['status']!=''){
            $object = $object->where("list.status",$reqData['status']);
        } 

        if(isset($reqData['page'])&&$reqData['page']){
            $page = $reqData['page'];
        }
        else{
            $page = 1;
        }
        $info = $object
            ->field([
                "list.id",
                "list.import_user_id as importUserId",
                "base1.name as importUserName",
                "list.examine_user_id as examinUserId",
                "base2.name as examinUserName",
                "list.final_user_id as findUserId",
                "base3.name as finalUsername",
                "list.title",
                "list.create_time as createTime",
                "list.update_time as updateTime",
                "list.status",
            ])
            ->order("list.status asc,list.id desc")
            ->paginate($reqData['pageSize'], false, ['page '=>$page]);
            // echo $object->getLastSql();exit;
        return $info;
    }  

    /**
     * [caseAllList description]
     * @param  [type] $userId  [description]
     * @param  [type] $reqData [description]
     * @return [type]          [description]
     */
    public function caseAllList($userId,$reqData){
        //1查询用户角色
        $roleOb = new \app\services\model\AdminRoles();
        $role = $roleOb->roleByRoleid($reqData['roleId']);
        if(!$role){
            $ret['status'] = 2001;
            return $ret;
        }
        $roleName = $role['role'];
        // $config = Config::get('api')['roleCase'];
        // $info = $config[$roleName];
        // if(!$info){
        //     $ret['status'] = 3001;
        //     return $ret;
        // }
        // echo $info;exit;
        $object = Db::table("case_list list")
            ->join("admin_users base1",'list.import_user_id = base1.id','left')
            ->join("admin_users base2",'list.examine_user_id = base2.id','left')
            ->join("admin_users base3",'list.final_user_id = base3.id','left')
            ->where("list.status",">=",0);

       
        if(isset($reqData['title']) && $reqData['title']!=''){
            $object = $object->whereLike("list.title","%".$reqData['title']."%");
        } 

        if($reqData['status']!=''){
            $object = $object->where("list.status",$reqData['status']);
        } 

        if(isset($reqData['page'])&&$reqData['page']){
            $page = $reqData['page'];
        }
        else{
            $page = 1;
        }
        $info = $object
            ->field([
                "list.id",
                "list.import_user_id as importUserId",
                "base1.name as importUserName",
                "list.examine_user_id as examinUserId",
                "base2.name as examinUserName",
                "list.final_user_id as finalUserId",
                "base3.name as finalUserName",
                "list.title",
                "list.create_time as createTime",
                "list.update_time as updateTime",
                "list.status",
            ])
            // ->order("status asc,id desc")
            ->order("list.update_time desc")
            ->paginate($reqData['pageSize'], false, ['page '=>$page]);
            // echo $object->getLastSql();exit;
        $ret['status'] = 0;
        $ret['info'] = $info;
        return $ret;
    }

    /**
     * [caseChnageList description]
     * @param  [type] $userId  [description]
     * @param  [type] $reqData [description]
     * @return [type]          [description]
     */
    public function caseChangeList($userId,$reqData){
        
        // echo $info;exit;
        $object = Db::table("case_change change")
            ->join("admin_users base",'change.user_id = base.id','left')
            ->where("change.case_id",$reqData['caseId']);

       
        if(isset($reqData['page'])&&$reqData['page']){
            $page = $reqData['page'];
        }
        else{
            $page = 1;
        }
        $info = $object
            ->field([
                "change.id",
                "change.user_id as userId",
                "change.before",
                "change.after",
                "change.q_column as qColumn",
                "change.q_reason as qReason",
                "change.create_time as createTime" ,
                "base.name as userName",
            ])
            ->order("id desc")
            ->paginate($reqData['pageSize'], false, ['page '=>$page]);
            // echo $object->getLastSql();exit;
        return $info;
    }

     /**
     * [caseBaseDetail description]
     * @param  [type] $userId  [description]
     * @param  [type] $reqData [description]
     * @return [type]          [description]
     */
    public function caseBaseDetail($userId,$reqData){
         $object = Db::table("case_list");
         $info = $object->where('id',$reqData['caseId'])->find();
         return $info;
    }

    /**
     * [caseDetail description]
     * @param  [type] $userId  [description]
     * @param  [type] $reqData [description]
     * @return [type]          [description]
     */
    public function caseDetail($userId,$reqData){
        //1查询用户角色
        $roleOb = new \app\services\model\AdminRoles();
        $role = $roleOb->roleByRoleid($reqData['roleId']);
        if(!$role){
            $ret['status'] = 2001;
            return $ret;
        }
        $roleName = $role['role'];
        // $config = Config::get('api')['roleCase'];
        // var_dump($config);exit;
        // $info = $config[$roleName];
        // if(!$info){
        //     $ret['status'] = 3001;
        //     return $ret;
        // }
        //2查询基础信息
        $object = Db::table("case_list case")
            ->join("admin_users base1",'case.import_user_id = base1.id','left')
            ->join("admin_users base2",'case.examine_user_id = base2.id','left')
            ->join("admin_users base3",'case.final_user_id = base3.id','left');
        // if($info=="import"){
        //     //录入员
        //     $object = $object->where("import_user_id",$userId);             
        // }
        // else if($info=='examine'){
        //     //初审员
        //     $object = $object->where("");
        // }
        // else if($info=='final'){
        //     // 终审
        //     $object = $object->where(""); 
        // }
        // else{
        // }
        $object = $object->where("(import_user_id =$userId or (examine_user_id =$userId or examine_user_id =0) or (final_user_id = $userId or  final_user_id=0))"); 

        $object = $object->where("case.id",$reqData['caseId'])
            ->where("case.status",">",-1); 
        $info = $object
            ->field([
                "case.id",
                "case.import_user_id as importUserId",
                "base1.name as importUserName",
                "case.examine_user_id as examinUserId",
                "base2.name as examinUserName",
                "case.final_user_id as finalUserId",
                "base3.name as finalUserName",
                "case.title",
                "case.create_time as createTime",
                "case.update_time as updateTime",
                "case.status",
                "case.title",
                "case.excel_url as excelUrl",
                "case.sheet_count as sheetCount",
            ])
            ->find();
        $ret['out_data'] = $info;
        if(!$info){
            $ret['status'] = 2008;
            return $ret;            
        }
                // echo $object->getLastSql();exit;

        //3查询详细信息
        $sheetNameArr = array();
        $tableName = $this->getCaseListElement($reqData['caseId']);
        $tableNameStr = "$tableName element";
        $eleObject = Db::table($tableNameStr)
            ->join("case_change changeInfo","changeInfo.case_id = element.case_id and changeInfo.q_column = element.key","left") 
            ->join("admin_users users","changeInfo.user_id = users.id ","left") 
            ->where("element.case_id",$reqData['caseId']);
        $elementInfo = $eleObject
            ->order("element.id asc,changeInfo.id asc")
            ->field([
                 "element.id",
                 "element.key",
                 "element.value",
                 "element.create_time as createTime",
                 "element.sheet",
                 "element.sheet_name as sheetName",
                 "changeInfo.id as changeId",
                 "changeInfo.user_id as changeUserId",
                 "users.name as changeAccount",
                 "changeInfo.before as changeBefore",
                 "changeInfo.after as changeAfter",
                 "changeInfo.q_column as changeColumn",
                 "changeInfo.q_reason as changeReason",
                 "changeInfo.create_time as changeCreateTime",
            ])
            ->select();

        if($elementInfo){
            $retElement = array();
            $markCount = 0;
            $retElement[$markCount]["id"] = $elementInfo[$markCount]['id'];
            $retElement[$markCount]["key"] = $elementInfo[$markCount]['key'];
            $retElement[$markCount]["value"] = $elementInfo[$markCount]['value'];
            $retElement[$markCount]["createTime"] = $elementInfo[$markCount]['createTime'];
            $retElement[$markCount]["sheet"] = $elementInfo[$markCount]['sheet'];
            $retElement[$markCount]["sheetName"] = $elementInfo[$markCount]['sheetName'];
            $retElement[$markCount]["keyOne"]='';
            $retElement[$markCount]["keyTwo"]='';
            $retElement[$markCount]["keyThree"]='';
            $retElement[$markCount]['question'] = array();
            $tempArr = array();
            $tempId = $elementInfo[0]['id']; 
            foreach ($elementInfo as $key => $value) {  
                // echo $markCount;
                array_push($sheetNameArr,$value['sheetName']);
                if($value['id']==$tempId){
                    $temp = $this->oneDetail($value);
                    array_push($tempArr,$temp);
                }
                else{
                    if(!empty($tempArr[0]['changeId'])){
                        $retElement[$markCount]['change'] = $tempArr;
                    }
                    else{
                        $retElement[$markCount]['change'] =array();
                    }
                    $markCount++;
                    //下一组
                    $retElement[$markCount]["id"] = $value['id'];
                    $retElement[$markCount]["key"] = $value['key'];
                    $retElement[$markCount]["value"] = $value['value'];
                    $retElement[$markCount]["createTime"] = $value['createTime'];
                    $retElement[$markCount]["sheet"] = $value['sheet'];
                    $retElement[$markCount]["sheetName"] = $value['sheetName'];
                    $retElement[$markCount]["keyOne"]='';
                    $retElement[$markCount]["keyTwo"]='';
                    $retElement[$markCount]["keyThree"]='';
                    $retElement[$markCount]['question'] = array();

                    $tempId = $value['id'];
                    $temp = $tempArr = array();
                    $temp = $this->oneDetail($value);
                    array_push($tempArr,$temp);
                }
            } 
            if(!empty($tempArr[0]['changeId'])){
                $retElement[$markCount]['change'] = $tempArr;
            }
            else{
                $retElement[$markCount]['change'] = array();   
            }
        }
        
        $sheetNameArr = array_unique($sheetNameArr);

        $ret['out_data']['sheetNameArray'] = implode(';', $sheetNameArr);
        // var_dump($ret['out_data']);exit;
        //4查询所有的质疑，在于element一个个的匹配起来
        $quObject = Db::table("case_question")
            ->where("case_id",$reqData['caseId']);
        $questionInfo = $quObject
            ->field([
                 "id as qId",
                 "q_column as qColumn",
                 "q_type as qType",
                 "q_reason as qReason",
                 "create_time as qCreateTime",
            ])
            ->select();
        if($questionInfo){
            $markQCount = 0;
            $questionTmpColumn = $questionInfo[$markQCount]['qColumn'];
            $questionArr = array();            
            $questionArr[$markQCount]['qColumn'] = $questionTmpColumn;
            $questionArr[$markQCount]['question'] = array();
            $tempQArr = array();
            foreach ($questionInfo as $key => $value) {
                if($questionTmpColumn==$value['qColumn']){
                    $tempQArr['qId'] = $value['qId'];
                    $tempQArr['qType'] = $value['qType'];
                    $tempQArr['qReason'] = $value['qReason'];
                    $tempQArr['qCreateTime'] = $value['qCreateTime'];
                    array_push($questionArr[$markQCount]['question'],$tempQArr);
                }
                else{
                    $markQCount++;
                    $questionTmpColumn = $value['qColumn'];
                    $questionArr[$markQCount]['qColumn'] = $questionTmpColumn;
                    $questionArr[$markQCount]['question'] = array();
                    $tempQArr['qId'] = $value['qId'];
                    $tempQArr['qType'] = $value['qType'];
                    $tempQArr['qReason'] = $value['qReason'];
                    $tempQArr['qCreateTime'] = $value['qCreateTime'];
                    array_push($questionArr[$markQCount]['question'],$tempQArr);
                }
            }    
            // var_dump($retElement);exit;
            foreach ($questionArr as $key => $value) {
                foreach ($retElement as $key1 => $value1) {
                    if($value1['key'] == $value['qColumn'])   {
                        $retElement[$key1]['question'] = $value['question'];
                        continue;
                    }
                }
            }   



        }

        $ret['data']['element'] = $retElement;
        //5查询附件信息(注意加上源文件) 
        $attObject = Db::table("case_attachment")
            ->where("case_id",$reqData['caseId']);
        $attachmentInfo = $attObject
            ->field([
                 "id",
                 "title",
                 "url",
            ])
            ->order("id desc")
            ->select();
            // var_dump($attachmentInfo);exit;
        $sourceFile['id'] =  0;
        $sourceFile['title'] =  $ret['out_data']['title'];
        $sourceFile['url'] =  $ret['out_data']['excelUrl'];
        array_push($attachmentInfo,$sourceFile);
        $ret['data']['attachment'] = $attachmentInfo;
        return $ret;
    }

    private function oneDetail($value){

        $temp['changeId'] = $value['changeId'];
        $temp['changeUserId'] = $value['changeUserId'];
        $temp['changeBefore'] = $value['changeBefore'];
        $temp['changeAfter'] = $value['changeAfter'];
        $temp['changeColumn'] = $value['changeColumn'];
        $temp['changeCreateTime'] = $value['changeCreateTime'];
        $temp['changeAccount'] = $value['changeAccount'];
        $temp['changeReason'] = $value['changeReason'];
        return $temp;
    }

    /**
     * addOneCase
     */
    public function addOneCase($userId,$list){
        $time = date("Y-m-d H:i:s");
        Db::startTrans();
        $ob = new \app\services\model\CaseList([
            "import_user_id"=>$userId,
            "title"=>$list['fileName'],
            "create_time"=>$time,
            "excel_url"=>$list['dburl'],
            "sheet_count"=>$list['excelSheetCount'],
        ]);
        if(!$ob->save()){
            Db::rollback();
            trace("create case error");
            return 1004;
        }
        $caseId = $ob->id;
        $emptyColumnCount = $totalColumnCount = 0;
        // var_dump($list['excelData']);exit;
        $emelentTable = $this->getCaseListElement($caseId);
        $allData = array();
        $table = Db::table($emelentTable);
        foreach ($list['excelData'] as $key => $value) {
            $sheet = $value['sheet'];
            $sheetName = $value['sheetName'];
            foreach ($value['data'] as $key2 => $value2) {
                // var_dump($value2);exit;
                if(!empty($value2[0])){

                    $temp['case_id'] = $caseId;
                    $temp['key'] = isset($value2[0])?$value2[0]:'';
                    $temp['value'] = isset($value2[1])?$value2[1]:'';
                    $temp['sheet'] = $sheet;
                    $temp['sheet_name'] = $sheetName;
                    array_push($allData, $temp);
                    if(empty($value2[1])){
                        $emptyColumnCount++;
                    }   
                    $totalColumnCount++;
                }
            }

        }
        // var_dump($allData);exit;
        if(!$table->insertAll($allData)){
            trace("insert data,".json_encode($allData));
            Db::rollback();
            trace("create case element error");
            return 1004;

        }
        if($totalColumnCount || $emptyColumnCount){
             $newOb = Db::table("case_list")->where("id",$caseId);
             $update = $newOb->update([
                "empty_column_count"=>$emptyColumnCount,
                "total_column_count"=>$totalColumnCount,
             ]);
             if(!$update){
                Db::rollback();
                trace("update case element error");
                return 1004;
             }
        }
        //添加一条基础信息
        
        $eleObject = Db::table("case_status_log");
        $newLog['case_id'] = $caseId;
        $newLog['user_id'] = $userId;
        $newLog['user_role_name'] = "录入员";
        $newLog['status'] = 0; 
        if(!$eleObject->insert($newLog)){
            Db::rollback();
            trace("insert log case element error");
            return 1004;
         }
        Db::commit();
        return 0;
    }

    /**
     * [queryBeforeElementValue description]
     * @return [type] [description]
     */
    public function queryBeforeElementValue($caseId,$key){
        $table = $this->getCaseListElement($caseId);
        $ob = Db::table($table)
            ->where("case_id",$caseId)
            ->where("key",$key);
        $info = $ob->field([
            "id",
            "value",
            ])
            ->find();
        return $info;
    }
    /**
     * [queryBeforeElementValue description]
     * @return [type] [description]
     */
    public function queryElementCount($caseId){
        $table = $this->getCaseListElement($caseId);
        $ob = Db::table($table)
            ->where("case_id",$caseId);
        $info = $ob->field([
            "count(id) as totalCount",
            ])
            ->find();
        return $info['totalCount'];
    }

    /**
     * [getCaseListElement description]
     * @return [type] [description]
     */
    private function getCaseListElement($caseId){
        $elementCount = Config::get('api')['caseListCount'];
        return "case_list_element_" . ($caseId % $elementCount );
    }

    /**
     * [addCaseAttachment description]
     * @param [type] $userId [description]
     * @param [type] $upRet  [description]
     */
    public function addCaseAttachment($userId,$upRet){         
        Db::startTrans();
        try{
            $ob = new \app\services\model\CaseAttachment([
                "case_id"=>$upRet['caseId'],
                "title"=>$upRet['fileName'],
                "url"=>$upRet['url'],
                "initial"=>$this->getInitial($upRet['fileName']),
            ]);
            if(!$ob->save()){
                Db::rollback();
                trace("create case error");
                return 1006;
            }
        }
        catch(\Exception $e){
            Db::rollback();
            return 1006;
        }
        Db::commit();
        return 0;
    }

    /**
     * [获取首字母 description]
     * @param  [type] $name [description]
     * @return [type]       [description]
     */
    public function getInitial($name){
        $str= iconv("UTF-8","GBK", $name);//如果程序是gbk的，此行就要注释掉 
        if (preg_match("/^[\x7f-\xff]/", $str)) 
        { 
            $fchar=ord($str{0}); 
            if($fchar>=ord("A") and $fchar<=ord("z") )return strtoupper($str{0}); 
            $a = $str; 
            $val=ord($a{0})*256+ord($a{1})-65536; 
            if($val>=-20319 and $val<=-20284)return "A"; 
            if($val>=-20283 and $val<=-19776)return "B"; 
            if($val>=-19775 and $val<=-19219)return "C"; 
            if($val>=-19218 and $val<=-18711)return "D"; 
            if($val>=-18710 and $val<=-18527)return "E"; 
            if($val>=-18526 and $val<=-18240)return "F"; 
            if($val>=-18239 and $val<=-17923)return "G"; 
            if($val>=-17922 and $val<=-17418)return "H"; 
            if($val>=-17417 and $val<=-16475)return "J"; 
            if($val>=-16474 and $val<=-16213)return "K"; 
            if($val>=-16212 and $val<=-15641)return "L"; 
            if($val>=-15640 and $val<=-15166)return "M"; 
            if($val>=-15165 and $val<=-14923)return "N"; 
            if($val>=-14922 and $val<=-14915)return "O"; 
            if($val>=-14914 and $val<=-14631)return "P"; 
            if($val>=-14630 and $val<=-14150)return "Q"; 
            if($val>=-14149 and $val<=-14091)return "R"; 
            if($val>=-14090 and $val<=-13319)return "S"; 
            if($val>=-13318 and $val<=-12839)return "T"; 
            if($val>=-12838 and $val<=-12557)return "W"; 
            if($val>=-12556 and $val<=-11848)return "X"; 
            if($val>=-11847 and $val<=-11056)return "Y"; 
            if($val>=-11055 and $val<=-10247)return "Z"; 
        }  
        else{
            return "Z";
        }
    
    }
    /**
     * [queryByUseridAndCaseId description]
     * @param  [type] $userId [description]
     * @param  [type] $caseId [description]
     * @return [type]         [description]
     */
    public function queryCaseId($caseId){
        $ob = Db::table("case_list")
            ->where("id",$caseId)
            ->field([
                "id",
                "import_user_id",
                "examine_user_id",
                "final_user_id",
                "status",
            ]);
        return $ob->find();
    }

    /**
     * [updateElementValue description]
     * @param  [type] $caseId [description]
     * @param  [type] $key    [description]
     * @param  [type] $value  [description]
     * @return [type]         [description]
     */
    public function updateElementValue($caseId,$key,$value){
        $tableName = $this->getCaseListElement($caseId);
        $ob = Db::table($tableName)
            ->where("case_id",$caseId)
            ->where("key",$key);
        $new['value'] = $value;
        $ret = $ob->update($new);
        if($ret){
            return 0;
        }
        else{
            return 1004;
        }

    }

    /**
     * [deleteCase description]
     * @param  [type] $caseId       [description]
     * @param  [type] $attachmentId [description]
     * @return [type]               [description]
     */
    public function deleteCase($caseId){
        $ob = Db::table("case_list")
        ->where("id",$caseId);
        $new['status'] = -1;
        $ret = $ob->update($new);
        return $ret;
    }

    /**
     * [elementDetail description]
     * @param  [type] $caseId [description]
     * @return [type]         [description]
     */
    public function elementDetail($caseId){
        $tableName = $this->getCaseListElement($caseId);
        $tableNameStr = "$tableName element";
        $eleObject = Db::table($tableNameStr)
            ->where("element.case_id",$caseId);
        $elementInfo = $eleObject
            ->field([
                 "element.id",
                 "element.key",
                 "element.value",
                 "element.create_time as createTime",
                 "element.sheet",
                 "element.sheet_name as sheetName"
            ])
            ->select();
            // echo $eleObject->getLastSql();exit;
            // var_dump($elementInfo);exit;
        return $elementInfo;
    }  

    /**
     * [elementFirstChange description]
     * @param  [type] $caseId [description]
     * @return [type]         [description]
     */
    public function elementFirstChange($caseId){ 
        $tableNameStr = "case_change";
        $eleObject = Db::table($tableNameStr)
            ->where("case_id",$caseId);
        $elementInfo = $eleObject
            ->group("q_column")
            ->field([
                 "q_column",
                 "before", 
            ])
            ->select();
            // echo $eleObject->getLastSql();exit;
            // var_dump($elementInfo);exit;
        return $elementInfo;
    }  

    /**
     * [caseLog description]
     * @param  [type] $caseId [description]
     * @return [type]         [description]
     */
    public function caseLog($userId,$reqData){
        $eleObject = Db::table("case_status_log log")
            ->join("admin_users user","log.user_id = user.id",'left')
            ->where("log.case_id",$reqData['caseId']);
        $elementInfo = $eleObject
            ->field([
                 "log.id",
                 "log.case_id as caseId",
                 "log.user_id as userId",
                 "log.user_role_name as roleName",
                 "log.status",
                 "log.create_time as createTime",
                 "user.name as userName",
            ])
            ->order("log.id desc")
            ->select();
            // echo $eleObject->getLastSql();exit;
            // var_dump($elementInfo);exit;
        return $elementInfo;
    }  
    
    /**
     * [changeLog description]
     * @param  [type] $caseId [description]
     * @return [type]         [description]
     */
    public function changeLog($userId,$reqData){
        $eleObject = Db::table("case_change")
            ->where("case_id",$reqData['caseId']);
        $elementInfo = $eleObject
            ->field([
                 "id",
                 "before",
                 "after",
                 "q_column as qColumn",
                 "q_reason as qReason",
                 "add_time as addTime",
            ])
            ->order("add_time asc,id asc")
            ->select();
            // echo $eleObject->getLastSql();exit;
            // var_dump($elementInfo);exit;
        return $elementInfo;
    }  

    /**
     * [submitCase 提交初审]
     * @param  [type] $caseId [description]
     * @return [type]         [description]
     */
    public function submitCase($userId,$caseId,$roleName,$status,$data){
        trace("submitCase,userId:$userId,caseId:$caseId,roleName:$roleName,status:$status,data:".json_encode($data));
        $info = Db::table("case_list")->where("id",$caseId)->find();
        if($info){
            if(!in_array($info['status'], $data['lastStatus'])){
                return 3010;
            }
        }
        else{
            return 3004;
        }
        $examineStatus = 0;
        Db::startTrans();
        //1修改状态 不是新的病例，如果是，更改初审员
        $ob = Db::table("case_list")->where("id",$caseId);
        $new['status'] = $status;
        // if(!empty($data['newCase'])){
            if($roleName=='录入员'){
            }
            else if($roleName=='初审员'){
                $new['examine_user_id'] = $userId;            
                $examineStatus = 1;
            }
            else{
                $new['final_user_id'] = $userId;            
                $examineStatus = 0;
            }
        // }

        if(!$ob->update($new)){
            Db::rollback();
            trace("submitCase case error");
            return 1004;
        }
        //2日志        
        $ob = new \app\services\model\CaseStatusLog([
            "user_id"=>$userId,
            "case_id"=>$caseId,
            "user_role_name"=>$roleName,
            "status"=>$status,
            
        ]);
        if(!$ob->save()){
            Db::rollback();
            trace("create case log error");
            return 1004;
        } 
        Db::commit();
        //添加消息
        $noticeOb = new \app\services\model\Notice();
        if($status!=2){
            //status=2,代表是提交审核，暂时没消息
            if($examineStatus == 1){
                if($info['import_user_id']){
                //初审员通过初审
                $noticeOb->addOne($info['import_user_id'],'初审通过',"您提交的病例：".$info['title']."，通过初审");

                }
            }
            else{
                //终审员通过终审
                if($info['import_user_id']){
                    //导入员
                    $noticeOb->addOne($info['import_user_id'],'终审通过',"您提交的病例：".$info['title']." 终审通过");

                }
                if($info['examine_user_id']){
                // 初审员
                $noticeOb->addOne($info['examine_user_id'],'终审通过',"您提交终审的病例：".$info['title']." 终审通过");
                    
                }
            }
        }
        return 0;
    }

    /**
     * [submitQuestion 提交质疑]
     * @param  [type] $caseId [description]
     * @return [type]         [description]
     */
    public function submitQuestion($userId,$caseId,$roleName,$data){
        trace("model,submitQuestion,userId:$userId,caseId:$caseId,roleName:$roleName,data:".json_encode($data));
        $info = Db::table("case_list")->where("id",$caseId)->find();
        if($info){
            if($info['status']!=$data['lastStatus']){
                return 3010;
            }
        }
        else{
            return 3004;
        }
        $examineStatus = 0;
        Db::startTrans();
        //1修改状态 把当前case的初审员改为自己 
        $ob = Db::table("case_list")->where("id",$caseId);
        $new['status'] = $data['newStatus'];
        // var_dump($data);exit;
        // if(!empty($data['newCase'])){
            if($data['roleString'] == "examine"){
                $new['examine_user_id'] = $userId;
                $examineStatus = 1;
            }
            else if($data['roleString'] == "final"){
                $new['final_user_id'] = $userId;
                $examineStatus = 0;
            }
        // }

        if(!$ob->update($new)){
            Db::rollback();
            trace("submitQuestion case error");
            return 1004;
        }
        //2批量添加质疑
        $caseOb = new \app\services\model\CaseQuestion();
        $caseQ = array();
        $column_count = 0;
        foreach ($data['questionData'] as $key => $value) {
            $temp=array(
                "user_id"=>$userId,
                "case_id"=>$caseId,
                "user_role_name"=>$roleName,
                "q_column"=>isset($value['qColumn'])?$value['qColumn']:"",
                "q_type"=>isset($value['qType'])?$value['qType']:"",
                "q_reason"=>isset($value['qReason'])?$value['qReason']:"",
            );
            array_push($caseQ,$temp);
            $column_count++;
        }

        if(!$caseOb->insertAll($caseQ)){
            Db::rollback();
            trace("create CaseQuestion log error");
            return 1004;
        }
        // 3日志        
        $roleOb = new \app\services\model\CaseList();
        $totalColumnCount = $roleOb->queryElementCount($caseId);
        $ob = new \app\services\model\CaseStatusLog([
            "user_id"=>$userId,
            "case_id"=>$caseId,
            "user_role_name"=>$roleName,
            "status"=>$data['newLogStatus'],
            "column_count"=>$column_count,
            "column_total_count"=>$totalColumnCount,
            
        ]);
        if(!$ob->save()){
            Db::rollback();
            trace("create case log error");
            return 1004;
        }
        Db::commit();
        //添加消息
        $noticeOb = new \app\services\model\Notice();
        if($examineStatus == 1){
            //初审员发出质疑
            $noticeOb->addOne($info['import_user_id'],'初审被质疑',"您提交的病例：".$info['title']."，被初审员提出质疑");
        }
        else{
            //终审员发出质疑
            //导入员
            $noticeOb->addOne($info['import_user_id'],'终审被质疑',"您提交的病例：".$info['title']."，被终审员提出质疑");
            // 初审员
            $noticeOb->addOne($info['examine_user_id'],'终审被质疑',"您提交终审的病例：".$info['title']."，被终审员提出质疑");
        }
        return 0;
    }


      /**
     * [caseBaseList description]
     * @param  [type] $userId  [description]
     * @param  [type] $reqData [description]
     * @return [type]          [description]
     */
    public function caseBaseList($userId,$reqData){
        //1查询用户角色
        $roleOb = new \app\services\model\AdminRoles();
        $role = $roleOb->roleByRoleid($reqData['roleId']);
        if(!$role){
            $ret['status'] = 2001;
            return $ret;
        }
        $roleName = $role['role'];
        // $config = Config::get('api')['roleCase'];
        // var_dump($config);exit;
        // $info = $config[$roleName];
        // if(!$info){
        //     $ret['status'] = 3001;
        //     return $ret;
        // }
        // echo $info;exit;
        $object = Db::table("case_list list")
            ->where("status",">=",0);           
            
        if(!empty($reqData['title'])){
            $object = $object->whereLike("list.title","%".$reqData['title']."%");
        } 

        if($reqData['status']!=''){
            $object = $object->where("list.status",$reqData['status']);
        } 

        if(isset($reqData['page'])&&$reqData['page']){
            $page = $reqData['page'];
        }
        else{
            $page = 1;
        }
        $info = $object
            ->field([
                "list.id",
                "list.title",
            ])
            ->order("list.id desc")
            ->paginate($reqData['pageSize'], false, ['page '=>$page]);
            // echo $object->getLastSql();exit;
        return $info;
    }   
    /**
     * [caseQualityIndex description]
     * @param  [type] $userId [description]
     * @param  [type] $data   [description]
     * @return [type]         [description]
     */
    public function caseQualityIndex($userId,$reqData){
        $ob = Db::table("case_status_log");
        if(!empty($reqData['caseId'])){
            $ob = $ob->where('case_id',$reqData['caseId']);
        }
        if(!empty($reqData['sdate'])){
            $ob = $ob->where('create_time',">=",$reqData['sdate']);
        }
        if(!empty($reqData['edate'])){
            $timestamp = strtotime($reqData['edate']);
            if(!$timestamp){
                return 1002;
            }
            $edate = date("Y-m-d H:i:s",strtotime("+1 day",$timestamp));
            $ob = $ob->where('create_time',"<=",$edate);
        }
        $info = $ob->field([
                "count(id) as totalCount",
                "user_role_name",
                "status",
                "sum(column_count) as columnCount",  //质疑字段个数
                "sum(column_total_count) as columnTotalCount", //被质疑文件字段总数
            ])
            ->group("user_role_name,status")
            ->select(); 
        $data = array();
        $out_data = array();
        $out_data['import'] = $out_data['examine'] = $out_data['final'] = $out_data['success'] = $out_data['examineQuestion'] = $out_data['finalQuestion'] = 0;
        $retElement = array();
        $retElement['data'] = array();
        foreach ($info as $key => $value) {
            if($key==0){                
                $retElement['roleName'] = $value['user_role_name'];
                $temp['count'] = $value['totalCount'];
                $temp['columnCount'] = $value['columnCount'];
                $temp['status'] = $value['status'];
                array_push($retElement['data'] ,$temp);
            }
            else{
                if($retElement['roleName'] == $value['user_role_name']){
                    $temp['count'] = $value['totalCount'];
                    $temp['columnCount'] = $value['columnCount'];
                    $temp['status'] = $value['status'];
                    array_push($retElement['data'] ,$temp);

                }
                else{
                    array_push($data,$retElement);
                    $retElement = array();
                    $retElement['data'] = array();
                    $retElement['roleName'] = $value['user_role_name'];
                    $temp['count'] = $value['totalCount'];
                    $temp['columnCount'] = $value['columnCount'];
                    $temp['status'] = $value['status'];
                    array_push($retElement['data'] ,$temp);
                }

            }
            // 0待提交（数据录入），1审核失败（保留），2待初审，3待终审，4终审审核成功，6初审质疑，7终审质疑，10变更数据，11初审未通过（无，同状态6），12终审未通过（无，同状态7）
            
            switch ($value['status']) {
                case 0:
                    $out_data['import'] = $value['totalCount'];
                    break;
                // case 1:
                //     break;
                case 2:
                    $out_data['examine'] = $value['totalCount'];
                    break;
                case 3:
                    $out_data['final'] = $value['totalCount'];
                    break;
                case 4:
                    $out_data['success'] = $value['totalCount'];
                    break;
                case 6:
                    $out_data['examineQuestion'] = $value['columnCount'];
                    $out_data['examineTotalQuestion'] = $value['columnTotalCount'];
                    break;
                case 7:
                    $out_data['finalQuestion'] = $value['columnCount'];
                    $out_data['finalTotalQuestion'] = $value['columnTotalCount'];
                    break;
                // case 10:
                //     $out_data['success'] = $value['totalCount'];
                    break;
                default:
                    # code...
                    break;
            }
        }
        if($retElement){
            array_push($data,$retElement);            
        }

        //如果是单个病例，则查询各个角色的名字
        // var_dump($reqData);exit;
        if(!empty($reqData['caseId'])){
            $ob = Db::table('case_list list')
                ->join("admin_users user1","list.import_user_id = user1.id","left")
                ->join("admin_users user2","list.examine_user_id = user2.id","left")
                ->join("admin_users user3","list.final_user_id = user3.id","left");
            $userInfo = $ob->where("list.id",$reqData['caseId'])
                ->field([
                    "user1.name as importUserName",
                    "user2.name as examineUserName",
                    "user3.name as finalUserName"
                ])->find();
                // var_dump($userInfo);exit;
            if($userInfo){
                foreach ($data as $key => $value) {
                    if($value['roleName']=="录入员"){
                        $data[$key]['roleUserName'] = !empty($userInfo['importUserName'])?$userInfo['importUserName']:"";
                        continue;
                    }
                    if($value['roleName']=="初审员"){
                        $data[$key]['roleUserName'] = !empty($userInfo['examineUserName'])?$userInfo['examineUserName']:"";
                        continue;   
                    }
                    if($value['roleName']=="终审员"){
                        $data[$key]['roleUserName'] = !empty($userInfo['finalUserName'])?$userInfo['finalUserName']:"";
                        continue;
                    }
                }
            }
        }
        $ret['data'] = $data;
        // 1211 客户沟通  初审通过率：初审通过的/导入分数，   终审通过率：终审通过的/导入分数
        $real_out_data['examineTotalCount'] = $out_data['import'];
        $real_out_data['examineUnPass'] = $out_data['import'] - $out_data['final'] ;

        $real_out_data['finalTotalCount'] = $out_data['import'];
        $real_out_data['finalUnPass'] = $out_data['import'] - $out_data['success'];
        
        $real_out_data['examineQuestionPass'] = isset($out_data['examineTotalQuestion'])?$out_data['examineTotalQuestion']:0;
        $real_out_data['examineQuestionUnPass'] =  isset($out_data['examineQuestion'] )?$out_data['examineQuestion']:0;
        
        $real_out_data['finalQuestionPass'] = isset($out_data['finalTotalQuestion'])?$out_data['finalTotalQuestion']:0;
        $real_out_data['finalQuestionUnPass'] = isset($out_data['finalQuestion'])?$out_data['finalQuestion']:0;
        $ret['out_data'] = $real_out_data;

        return $ret;
    }

    /**
     * [caseQualityOther description]
     * @param  [type] $userId  [description]
     * @param  [type] $reqData [description]
     * @return [type]          [description]
     */
    public function caseQualityOther($userId,$reqData){
        if(empty($reqData['caseId'])){
            $ret['status'] = 1002;
            return $ret;
        }

        //曹宇:空数据:值为空的字段占总字段的比例   异常数据：被质疑过的均为异常数据与总字段比例 有效数据：没有被质疑过且不为空的数据
        //1总字段数
        $case = Db::table("case_list")->where("id",$reqData['caseId'])->find();
        if(!$case){
            $ret['status'] = 3004;
            return $ret;
        }
        $ret['out_data']['totalColumnCount'] = $case['total_column_count'];
        //2空字段个数
        $ret['out_data']['emptyColumnCount'] = $case['empty_column_count'];
        //3没有修改过的字段
        $caseChange = Db::table("case_change")
            ->group("q_column")
            ->where("case_id",$reqData['caseId'])
            ->field(["id"])
            ->select();
        $ret['out_data']['changeColumnCount'] = count($caseChange);
        
        //4修改过的字段
        $ret['out_data']['unChangeColumnCount'] = $case['total_column_count'] - $ret['out_data']['changeColumnCount'];
        //
        //5质疑类型，前几个+其他
        $caseQuestion = Db::table("case_question")
            ->group("q_type")
            ->order("totalCount desc")
            ->where("case_id",$reqData['caseId'])
            ->field(["id","q_type","count(id) as totalCount"])
            ->select();
        $questionArr = array();
        $ret['out_data']['question'] = array();
        // var_dump($caseQuestion);exit;
        $tempCount = 0;
        $totalCount = 0;
        // var_dump($caseQuestion);exit;
        foreach ($caseQuestion as $key => $value) {
            $totalCount +=$value['totalCount'];
            if($key>=5){
            }
            else{
                $questionArr['qType'] = $value['q_type'];
                $questionArr['totalCount'] = $value['totalCount'];
                $tempCount +=$value['totalCount'];
                array_push($ret['out_data']['question'],$questionArr);   
            }
        }
        $questionArr['qType'] = '其他';
        $questionArr['totalCount'] = $totalCount - $tempCount;
        array_push($ret['out_data']['question'],$questionArr);
        $ret['status'] = 0;
        return $ret;
    }

    /**
     * [elementChangeDetail 修改过的字段]
     * @param  [type] $caseId [description]
     * @return [type]         [description]
     */
    public function elementChange($caseId){
        $eleObject = Db::table("case_change")
            ->where("case_id",$caseId);
        $elementInfo = $eleObject
            ->group("q_column")
            ->field([
                 "q_column as qColumn",
            ])
            ->select();
            // echo $eleObject->getLastSql();exit;
            // var_dump($elementInfo);exit;
        return $elementInfo;
    }   

}